
# Importing the libraries
import os
import numpy as np
import time  # For time-related operations
import mlflow  # For experiment tracking and management
import pandas as pd  # For data manipulation and analysis
import plotly.express as px  # For creating interactive visualizations
import plotly.io as pio  # For configuring plot settings
import plotly.graph_objects as go  # For creating custom visualizations
from plotly.subplots import make_subplots  # For creating subplots

from wordcloud import WordCloud, STOPWORDS  # For generating word clouds and handling stopwords


from pyFuncs import mlflow_setup as ml
from pyFuncs import plotly_config as pc


pd.set_option('display.max_columns', None)


pc.configure_plotly()


use_mlflow = True

def return_mlflow_config(exp_name):
    """
    Return the mlflow config object
    """
    mlflow_config = ml.MLflowConfig(experiment_name=exp_name)
    # if experiment does not exist, create it
    if mlflow.get_experiment_by_name(mlflow_config.experiment_name) is None:
        mlflow_config.configure_mlflow()
    # else:
    #     mlflow_config.create_experiment()
    mlflow_config.get_experiment_id()
    return mlflow_config

if use_mlflow:
    # 0. Configure MLflow
    mlflow_config = return_mlflow_config("Consumer_Complaints")
    # start the run for this experiment
    mlflow.start_run(experiment_id=mlflow_config.experiment_id)
    # Save run id in a variable
    run_id = mlflow.active_run().info.run_id

2023/07/16 23:30:54 INFO mlflow.tracking.fluent: Experiment with name 'Consumer_Complaints' does not exist. Creating a new experiment.

Experiment ID: 1


# Specify the URL of the Customer Complaint Database
database_url = 'https://files.consumerfinance.gov/ccdb/complaints.csv.zip'

# Define the file paths
full_dataset_file_path = 'input_data/customer_complaints_full.csv'
sample_dataset_file_path = 'input_data/customer_complaints_sample.csv'

if os.path.exists(full_dataset_file_path) and os.path.exists(sample_dataset_file_path):
    # If both files already exist, load them into DataFrames
    inDF = pd.read_csv(full_dataset_file_path)
    sample_dataset_df = pd.read_csv(sample_dataset_file_path)
else:
    # Read the Customer Complaint Database from the URL
    complaints_df = pd.read_csv(database_url, compression='zip')
    
    # This dataset is really huge, so will limit it to last 2 years data.

    # Convert the 'Date Received' column to datetime type
    complaints_df['Date received'] = pd.to_datetime(complaints_df['Date received'])

    # Get the current year
    current_year = pd.Timestamp.now().year

    # Calculate the start and end dates for the last two years
    start_date = pd.Timestamp(year=current_year - 2, month=1, day=1)
    end_date = pd.Timestamp(year=current_year, month=12, day=31)

    # Filter the DataFrame to include only the last two years' data
    filtered_df = complaints_df[(complaints_df['Date received'] >= start_date) & 
                                (complaints_df['Date received'] <= end_date)]

    # Create the 'input_data' folder if it does not exist
    os.makedirs('input_data', exist_ok=True)

    # Save the filtered DataFrame as a CSV file (full dataset)
    filtered_df.to_csv(full_dataset_file_path, index=False)

    # Save a sample of the filtered DataFrame as a CSV file

    sample_size = int(len(filtered_df) * 0.01)  # Calculate 0.01% of the dataframe's length
    sample_df = filtered_df.head(sample_size)  # Get the first 0.01% of the dataframe
    sample_df.to_csv(sample_dataset_file_path, index=False)  # Save the dataframe to a csv file

    # Assign the filtered DataFrame to the full dataset DataFrame
    inDF = filtered_df.copy()

# full_dataset_df contains the full filtered dataset
# sample_dataset_df contains a sample of the filtered dataset


def preprocess_data(inDF):
    """
    Preprocess the data. Below are the steps:
    1. Drop the Rows if Consumer Complaint Narrative is NaN or Null
    2. Drop the Duplicates on Consumer Complaint Narrative
    3. Change format of Date received and Date sent to company
    4. Drop rows if Date received is greater than Date sent to company
    5. The columns `Date received` and `Date sent to company` were used to create a new column `Days to resolve`
    6. Column "Consumer disputed?" has only 0 values. Hence, dropping it.
    :param inDF: pandas dataframe representing the input data
    :return: pandas dataframe representing the preprocessed data (out_df)
    """
    start_time = time.time()
    
    # 1. Drop Rows if Consumer Complaint Narrative is NaN or Null
    # Going forward, we will be using only Consumer Complaint Narratives
    preprocess_df = inDF.dropna(subset=['Consumer complaint narrative'])

    # 2. Drop Duplicates on Consumer Complaint Narrative
    preprocess_df = preprocess_df.drop_duplicates(subset=['Consumer complaint narrative'])

    # 3. Change format of Date received and Date sent to company
    preprocess_df['Date received'] = pd.to_datetime(preprocess_df['Date received'])
    preprocess_df['Date sent to company'] = pd.to_datetime(preprocess_df['Date sent to company'])

    # 4. Drop rows if Date received is greater than Date sent to company
    # Reason: Date received of consumer complaint should be less than Date sent to company.
    # If not, it is an error
    preprocess_df = preprocess_df[preprocess_df['Date received'] <= preprocess_df['Date sent to company']]

    # 5. The columns `Date received` and `Date sent to company` were used to create a new column `Days to resolve`
    # which is the number of days taken by the company to resolve the complaint.
    preprocess_df['Days to resolve'] = (preprocess_df['Date sent to company'] -
                                        preprocess_df['Date received']).dt.days + 1

    # 6. Column "Consumer disputed?" has only 0 values. Hence, dropping it.
    out_df = preprocess_df.drop(columns=['Consumer disputed?'])

    
    end_time = time.time()
    time_taken = end_time - start_time
    print("Preprocessing completed in {:.2f} seconds.".format(time_taken))

    return out_df

processDF = preprocess_data(inDF)

Preprocessing completed in 5.67 seconds.


def explore_product(processDF):
    """
    Explore the Product column and generate a bar graph representing the distribution of products 
    mentioned in consumer complaints.

    Parameters:
    processDF (pandas.DataFrame): Preprocessed DataFrame containing the consumer complaints data.

    Returns:
    None
    """
    data = processDF['Product'].value_counts()
    data = data.sort_values(ascending=False)

    fig = px.bar(data, x=data.index, y=data.values, color=data.index,
                 labels={'x': 'Product', 'y': 'Count', 'color': 'Product'},
                 title='Figure 4.1: Product By Highest Complaint Volume')

    fig.update_traces(texttemplate='%{y}', textposition='outside',
                      textfont_size=8)

    fig.add_annotation(x=0.5, y=1.1,
                       text="Total Complaints: " + str(data.sum()),
                       showarrow=False,
                       font=dict(size=12, color="black"),
                       xref="paper",
                       yref="paper")

    fig.update_xaxes(showticklabels=False)
    # replace 'index' with 'Product' on x-axis
    fig.update_layout(xaxis_title="Product")
    pio.show(fig)
    fig.write_html("plots/1.1_product_histogram.html")
    mlflow.log_artifact("plots/1.1_product_histogram.html")

    return None

explore_product(processDF)


def explore_issue(processDF):
    """
    Explore the "Issue" column of the preprocessed DataFrame and generate a bar graph.

    Parameters:
    processDF (pandas.DataFrame): Preprocessed DataFrame containing the consumer complaints data.

    Returns:
    None
    """
    data = processDF['Issue'].value_counts()
    data = data.sort_values(ascending=False)

    total_issues = len(data)
    top10_issues = data.index[:10]

    fig = px.bar(data[top10_issues], x=data[top10_issues].index,
                 y=data[top10_issues].values,
                 color=data[top10_issues].index,
                 labels={'x': 'Issue', 'y': 'Count', 'color': 'Issue'},
                 title='Figure 4.2: Showing Top 10 (out of {}) Issues by Highest Complaint Volume'.format(total_issues))

    fig.update_traces(texttemplate='%{y}', textposition='outside',
                      textfont_size=8)

    fig.add_annotation(x=0.5, y=1.1,
                       text="Total Complaints: " + str(data.sum()),
                       showarrow=False,
                       font=dict(size=12, color="black"),
                       xref="paper",
                       yref="paper")

    fig.update_xaxes(showticklabels=False)
    # replace 'index' with 'Issues' on x-axis
    fig.update_layout(xaxis_title="Issues")
    pio.show(fig)
    fig.write_html("plots/2.1_issue_histogram.html")
    mlflow.log_artifact("plots/2.1_issue_histogram.html")

explore_issue(processDF)


def explore_date_received(processDF):
    """
    Explore the Date received column using line chart
    :param processDF: DataFrame containing the preprocessed complaints data
    :return:
    """
    # Group the complaints by date received and calculate the count
    data_receive = processDF.groupby(pd.Grouper(key='Date received', freq='7D')).size().reset_index(name='Count')

    # Convert the 'Date received' column to datetime if it's not already in datetime format
    data_receive['Date received'] = pd.to_datetime(data_receive['Date received'])

    # Sort the DataFrame by 'Date received' in ascending order
    data_receive = data_receive.sort_values('Date received')

    # Now add the date the CFPB sent the complaint to the company
    # Group the complaints by date sent to company and calculate the count
    data_sent = processDF.groupby(pd.Grouper(key='Date sent to company', freq='7D')).size().reset_index(name='Count')
    data_sent['Date sent to company'] = pd.to_datetime(data_sent['Date sent to company'])
    data_sent = data_sent.sort_values('Date sent to company')

    # Using plotly to plot the graph with dotted lines for date received and date sent
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=data_receive['Date received'], y=data_receive['Count'],
                             mode='lines+markers',
                             name='Date received by the CFPB'))
    fig.add_trace(go.Scatter(x=data_sent['Date sent to company'], y=data_sent['Count'],
                             mode='lines+markers',
                             name='Date sent to company by the CFPB'))

    fig.update_yaxes(title=None)
    fig.update_xaxes(title=None)
    fig.update_layout(title_text='Figure 4.3.1: Total Complaints by Date Received and Date Sent to Company',
                      title_x=0.5,
                     legend=dict(
        orientation="h",
        yanchor="top",
        y=1.02,
        xanchor="left",
        x=0.01
    ))
    pio.show(fig)

    fig.write_html("plots/3.1_dates_line_chart.html")
    mlflow.log_artifact("plots/3.1_dates_line_chart.html")

    # Group the complaints by product and date received
    complaints_by_product_date = processDF.groupby(['Product', pd.Grouper(key='Date received', freq='7D')]).size().reset_index(name='Count')

    complaints_by_product_date['Date received'] = pd.to_datetime(complaints_by_product_date['Date received'])

    # Sort the DataFrame by product and date received
    complaints_by_product_date = complaints_by_product_date.sort_values(['Product', 'Date received'])

    # Plot the count of complaints for each product using go.Scatter
    fig = go.Figure()

    for product in complaints_by_product_date['Product'].unique():
        product_data = complaints_by_product_date[complaints_by_product_date['Product'] == product]
        fig.add_trace(go.Scatter(
            x=product_data['Date received'],
            y=product_data['Count'],
            mode='lines',
            name=product
        ))

    # Set the x-axis and y-axis labels
    fig.update_layout(
        xaxis_title='Date Received (7-Day Interval)',
        yaxis_title='Count of Complaints',
        title_text='Figure 4.3.2: Total Complaints by Date Received and Product',
        title_x=0.5,
        legend=dict(
        orientation="h",
        yanchor="top",
        y=1.02,
        xanchor="left",
        x=0.01
    )
    )
    pio.show(fig)

    fig.write_html("plots/3.2_dates_line_chart_product.html")
    mlflow.log_artifact("plots/3.2_dates_line_chart_product.html")


explore_date_received(processDF)


def explore_days_to_resolve(preprocessDF):
    """
    Explore the Days to resolve column using a histogram
    """
    # Group the complaints by days to resolve and calculate the count
    data = preprocessDF.groupby(['Days to resolve']).size().reset_index(name='Count')

    # Sort the DataFrame by days to resolve in ascending order
    data = data.sort_values('Days to resolve')

    # Lets create days interval of 25 days by calculating the min and max days to resolve
    min_days = data['Days to resolve'].min()
    max_days = data['Days to resolve'].max()

    data['Days to resolve'] = pd.cut(data['Days to resolve'], bins=[min_days, 25, 50, 75, 100, max_days],
                                        labels=['1-25', '25-50', '50-75', '75-100', '100 and beyond'])

    # Plot the histogram using plotly
    fig = px.bar(data, x='Days to resolve', y='Count', color='Days to resolve',
                    title='Total Complaints by Days to Resolve')

    # Set the x-axis and y-axis labels
    fig.update_layout(
        xaxis_title='Days to Resolve',
        yaxis_title='Count of Complaints',
        title_text='Figure 4.4: Total Complaints by Days to Resolve',
        title_x=0.5,
        showlegend=False
    )
    pio.show(fig)

    fig.write_html("plots/3.3_days_to_resolve_histogram.html")

    # Add the plot to MLflow
    mlflow.log_artifact("plots/3.3_days_to_resolve_histogram.html")
    
explore_days_to_resolve(processDF)


def explore_company_response(processDF):
    """
    Explore the Companies and company response columns
    :param processDF: DataFrame containing the preprocessed complaints data
    """
    # Group the complaints by company and company response
    company_response_df = processDF.groupby(['Company', 'Timely response?']).size().reset_index(name='Frequency')

    # Sort the DataFrame by company and count of responses
    company_response_df = company_response_df.sort_values(['Company', 'Frequency'], ascending=False)

    # Get the top 15 companies
    top15_companies = company_response_df['Company'].unique()[:15]

    # Get the data for only the top 15 companies
    company_response_df = company_response_df[company_response_df['Company'].isin(top15_companies)]

    # Using plotly to plot the graph with stacked bars
    fig = px.pie(company_response_df, values='Frequency', names='Company',
                 title="Figure 4.5.1: Top 15 Companies by Complaint Volume and Timely Response - Pie Chart")

    pio.show(fig)
    fig.write_html("plots/4.1_company_timely_response_pie_chart.html")
    mlflow.log_artifact("plots/4.1_company_timely_response_pie_chart.html")

    # Using plotly to plot the graph with stacked bars
    fig = px.bar(company_response_df, x="Company", y="Frequency", color="Timely response?",
                 title="Figure 4.5.2: Top 15 Companies by Complaint Volume and Timely Response - Bar Chart")

    # Add actual count on top of the bar as an int
    fig.update_traces(texttemplate='%{y}', textposition='outside', textfont_size=8)

    # Add all together total count at top of the plot
    fig.add_annotation(x=0.5, y=1.1, text="Total Complaints: " + str(company_response_df['Frequency'].sum()),
                       showarrow=False, font=dict(size=14, color="black"), xref="paper", yref="paper")

    # Set the order of categories on the x-axis
    fig.update_layout(xaxis={'categoryorder': 'total ascending'})

    # Position the legend on the left inside the figure
    fig.update_layout(legend=dict(
        orientation="h",
        yanchor="top",
        y=1.02,
        xanchor="left",
        x=0.01
    ))

    pio.show(fig)
    fig.write_html("plots/4.2_company_timely_response_bar_chart.html")
    mlflow.log_artifact("plots/4.2_company_timely_response_bar_chart.html")

    # Explore how companies respond to complaints using "Company response to consumer" only
    # Group the complaints by company response and count the number of complaints
    company_response_df = processDF.groupby('Company response to consumer').size().reset_index(name='Count')

    # Using plotly to plot the graph with stacked bars
    fig = px.bar(company_response_df, x="Company response to consumer", y="Count",
                 title="Figure 4.5.3: Company Response to Consumer")

    # Add actual count on top of the bar as an int
    fig.update_traces(texttemplate='%{y}', textposition='outside', textfont_size=8)

    # Add all together total count at top of the plot
    fig.add_annotation(x=0.5, y=1.1, text="Total Complaints: " + str(company_response_df['Count'].sum()),
                       showarrow=False, font=dict(size=14, color="black"), xref="paper", yref="paper")

    # Set the order of categories on the x-axis
    fig.update_layout(xaxis={'categoryorder': 'total ascending'})

    # Position the legend on the left inside the figure
    fig.update_layout(legend=dict(
        orientation="h",
        yanchor="top",
        y=1.02,
        xanchor="left",
        x=0.01
    ))

    pio.show(fig)
    fig.write_html("plots/4.3_company_response_bar_chart.html")
    mlflow.log_artifact("plots/4.3_company_response_bar_chart.html")

explore_company_response(processDF)


def explore_state(preprocessDF):
    """
    Explore the State column. We will use the layout of the United States to show the count.
    """
    # Group the complaints by state and count the number of complaints
    state_df = preprocessDF.groupby('State').size().reset_index(name='Count')

    # Using plotly to plot the graph with Choropleth and use the best template
    fig = go.Figure(data=go.Choropleth(
        locations=state_df['State'],  # Spatial coordinates
        z=state_df['Count'].astype(float),  # Data to be color-coded
        locationmode='USA-states',  # set of locations match entries in `locations`
        colorscale='Blues',
        colorbar_title="Count"
    ))

    fig.update_layout(
        title_text='Total Complaints by State',
        title_x=0.5,
        geo_scope='usa',  # limit map scope to USA
        template="plotly_white",
        coloraxis_colorbar=dict(
            title="Map shading: <br>Count of Complaints",
        )
    )
    pio.show(fig)

    fig.write_html("plots/4.4_usa_map_state.html")
    # Add the plot to MLflow
    mlflow.log_artifact("plots/4.4_usa_map_state.html")

    
explore_state(processDF)


def explore_consumer_complaint_narrative(preprocessDF):
    """
    Consumer complaint narrative is a free form text field.
    We will use wordcloud to explore this field with the help of the wordcloud library and Plotly
    """
    # Get the text from consumer complaint narrative removing stop words
    text = " ".join(review for review in preprocessDF['Consumer complaint narrative'].dropna())
    stopwords = set(STOPWORDS)

    # Also remove any XXXX kind of words
    stopwords.update(["XXXX", "XX", "xx", "xxxx", "xxxxxx", "xxxxxxxx", "xxxxxxxxx", "xxxxxxxxxx",
                      "XXXXXX", "XXXXXXXX"])

    # Generate a word cloud image
    wordcloud = WordCloud(stopwords=stopwords, background_color="white").generate(text)

    # Display the generated image with a nice plotly layout
    fig = px.imshow(wordcloud, template="plotly_white")
    fig.update_layout(
        title_text='Consumer Complaint Narrative Word Cloud',
        title_x=0.5
    )
    pio.show(fig)
    
    fig.write_html("plots/5.1_consumer_complaint_narrative_wordcloud.html")
    # Add the plot to MLflow
    mlflow.log_artifact("plots/5.1_consumer_complaint_narrative_wordcloud.html")    

    
explore_consumer_complaint_narrative(processDF.head(10000))

Data programming in Python (DSM020-2023-APR)¶

Coursework 1 Submission¶

Consumer Complaint Database¶

Overview¶

Motivation¶

Dataset Information¶

Usage¶

Dependencies¶

References¶

Step 1: Import Libraries¶

Overview¶

Libraries Used¶

Usage¶

Step 1.1: System Libraries¶

Step 1.2: User-defined Libraries¶

Step 1.3: Pandas Configuration¶

Step 1.4: Plotly Configuration¶

Step 1.5: MLflow Configuration Object¶

Step 2: Import Dataset¶

Overview¶

Dataset Description¶

Step 3: Data Preprocessing¶

Step 4: Explore Structured Columns¶

Step 4.1: Explore Product Column¶

Step 4.2: Explore Issue Column¶

Step 4.3: Explore Date Features¶

Step 4.4: Explore Days to Resolve¶

Step 4.5: Explore Company Response¶

Step 4.6: Explore response by state¶

Step 5: Explore Unstructured Columns¶

Conclusion¶